const { User, Dish, DateDish, UserDateDish, Price } = require("../db");
// , MealTypeDish, MealType
const Sequelize = require('sequelize');
const { Op } = require('sequelize');
const MEALTYPEENUM = {
    breakfast: '早餐',
    lunch: '午餐',
    dinner: '晚餐'
}
exports.addDateDish = async (req, res) => {
    const { dishes, date } = req.body;
    const { breakfast, lunch, dinner } = dishes;
    // breakfast, lunch, dinner 为三个数组，每个数组中包含dishId
    // date为日期
    try {
        const dateId = await DateDish.findOne({ where: { date } });
        if (!dateId) {
            // 新建dateDish
            const newDateDish = await DateDish.create({
                date,
                breakfastDishes: breakfast,
                lunchDishes: lunch,
                dinnerDishes: dinner
            });
            // const dateId = newDateDish.dataValues.id;
            // // DateDish 含有三个mealType,breakfast,lunch,dinner
            // DateDish.hasMany(MealType, {
            //     foreignKey: 'dateId',
            //     sourceKey: 'id'
            //   });
            //   MealType.belongsTo(DateDish, {
            //     foreignKey: 'dateId',
            //     targetKey: 'id'
            //   });
            // 新建mealType
            // const breakfastMealType = await MealType.create({ type: 'breakfast', dateId });
            // const lunchMealType = await MealType.create({ type: 'lunch', dateId });
            // const dinnerMealType = await MealType.create({ type: 'dinner', dateId });

            // 新建dish==>取消
            // for (let i = 0; i < breakfast.length; i++) {
            //     await MealTypeDish.create({ mealTypeId: breakfastMealType.dataValues.id, dishId: breakfast[i] });
            // }
            // for (let i = 0; i < lunch.length; i++) {
            //     await MealTypeDish.create({ mealTypeId: lunchMealType.dataValues.id, dishId: lunch[i] });
            // }
            // for (let i = 0; i < dinner.length; i++) {
            //     await MealTypeDish.create({ mealTypeId: dinnerMealType.dataValues.id, dishId: dinner[i] });
            // }
            res.json({ status: 200, message: '添加成功' });
        }
        else {
            // 更新dateDish
            await DateDish.update({
                breakfastDishes: breakfast,
                lunchDishes: lunch,
                dinnerDishes: dinner
            }, {
                where: { id: dateId.dataValues.id }
            })
            // const mealList = await MealType.findAll({ where: { dateId: dateId.dataValues.id } });
            // for (let i = 0; i < mealList.length; i++) {
            // const mealTypeDishList = await MealTypeDish.findAll({ where: { mealTypeId: mealList[i].dataValues.id } });
            // for (let j = 0; j < mealTypeDishList.length; j++) {
            //     await mealTypeDishList[j].destroy(); // 删除所有关联的mealTypeDish
            // }
            // }
            // 新建dish ： 确定mealList[0]为breakfast，mealList[1]为lunch，mealList[2]为dinner ==》取消
            // for (let i = 0; i < breakfast.length; i++) {
            //     // await MealTypeDish.create({ mealTypeId: mealList[0].dataValues.id, dishId: breakfast[i] });
            // }
            // for (let i = 0; i < lunch.length; i++) {
            //     // await MealTypeDish.create({ mealTypeId: mealList[1].dataValues.id, dishId: lunch[i] });
            // }
            // for (let i = 0; i < dinner.length; i++) {
            //     // await MealTypeDish.create({ mealTypeId: mealList[2].dataValues.id, dishId: dinner[i] });
            // }
            res.json({ status: 200, message: '更新成功' });
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.getAllDishes = async (req, res) => {
    try {
        const { name, page, pageSize } = req.body;
        const dishes = await Dish.findAll({
            where: {
                name: { [Op.like]: `%${name}%` }
            },
            offset: (page - 1) * pageSize,
            limit: pageSize,
            order: [['id', 'DESC']]
        })
        const count = await Dish.count({
            where: {
                name: { [Op.like]: `%${name}%` }
            }
        })
        res.json({
            status: 200,
            data: dishes,
            total: count
        });
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.getDateRangeDish = async (req, res) => {
    try {
        const { firstDay, lastDay } = req.body;
        console.log(firstDay, lastDay);

        const { id: userId } = req.user;
        if (firstDay && lastDay) {
            const dateRangeDish = await DateDish.findAll({
                where: {
                    date: {
                        [Op.between]: [firstDay, lastDay]
                    }
                },
                // include: {
                //     model: MealType,
                //     // include: {
                //     //     model: Dish
                //     // },
                // }
            });
            const result = {};
            const price = await Price.findOrCreate({
                where: {
                    id: 1
                },
                defaults: {
                    breakfast: 1, lunch: 3, dinner: 3,
                    breakfastTime: 8, lunchTime: '-10', dinnerTime: '-16'
                }
            })
            const promises = dateRangeDish.map(item => {
                return new Promise(async (resolve) => {
                    if (!result[item.date]) {
                        result[item.date] = {
                            breakfast: item.breakfastDishes,
                            lunch: item.lunchDishes,
                            dinner: item.dinnerDishes,
                            // companyNumber: item.dataValues.companyNumber,
                            // 大于当前日期7小时
                            // canOrder: new Date(item.date) > new Date().getTime() + 7 * 60 * 60 * 1000,
                            ifUserOrder: {
                                breakfast: false,
                                lunch: false,
                                dinner: false
                            },
                            count: {
                                breakfast: 0,
                                lunch: 0,
                                dinner: 0
                            },
                            canOrderTime: {
                                breakfast: new Date < new Date(new Date(item.date + ' 00:00:00').getTime() - price[0].dataValues.breakfastTime * 60 * 60 * 1000),
                                lunch: new Date < new Date(new Date(item.date + ' 00:00:00').getTime() - price[0].dataValues.lunchTime * 60 * 60 * 1000),
                                dinner: new Date < new Date(new Date(item.date + ' 00:00:00').getTime() - price[0].dataValues.dinnerTime * 60 * 60 * 1000)
                            }
                        };
                    }
                    // item.dataValues.MealTypes.forEach(mealType => {
                    //     mealType.dataValues.Dishes.forEach(dish => {
                    //         if (mealType.dataValues.type === 'breakfast') {
                    //             result[item.date].breakfast.push(dish.dataValues);
                    //         } else if (mealType.dataValues.type === 'lunch') {
                    //             result[item.date].lunch.push(dish.dataValues);
                    //         } else if (mealType.dataValues.type === 'dinner') {
                    //             result[item.date].dinner.push(dish.dataValues);
                    //         }
                    //     });
                    // });
                    const userDateDish = await UserDateDish.findOne({
                        where: {
                            userId,
                            dateId: item.dataValues.id
                        }
                    });
                    if (userDateDish) {
                        result[item.date].ifUserOrder.breakfast = userDateDish.dataValues.breakfast;
                        result[item.date].ifUserOrder.lunch = userDateDish.dataValues.lunch;
                        result[item.date].ifUserOrder.dinner = userDateDish.dataValues.dinner;
                    }

                    const breakfastCount = await UserDateDish.count({
                        where: {
                            dateId: item.dataValues.id,
                            breakfast: true
                        }
                    });
                    const lunchCount = await UserDateDish.count({
                        where: {
                            dateId: item.dataValues.id,
                            lunch: true
                        }
                    });
                    const dinnerCount = await UserDateDish.count({
                        where: {
                            dateId: item.dataValues.id,
                            dinner: true
                        }
                    })
                    result[item.date].count.breakfast = breakfastCount;
                    result[item.date].count.lunch = lunchCount;
                    result[item.date].count.dinner = dinnerCount;

                    resolve();
                });
            });

            await Promise.all(promises); // 等待所有的异步操作完成

            res.json({
                status: 200,
                data: result
            });
        } else {
            res.json({
                status: 200,
                data: []
            });
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.addDish = async (req, res) => {
    try {
        const { name, price, description, image } = req.body;
        const dish = await Dish.create({ name, price, description, image });
        res.json({ status: 200, message: '添加成功', data: dish });
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.deleteDish = async (req, res) => {
    try {
        const { id } = req.body;
        const dish = await Dish.destroy({ where: { id } });
        res.json({ status: 200, message: '删除成功', data: dish });
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.updateDish = async (req, res) => {
    try {
        const { id, name, price, description, image } = req.body;
        const dish = await Dish.update({ name, price, description, image }, { where: { id } });
        res.json({ status: 200, message: '更新成功', data: dish });
    }
    catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

exports.orderDate = async (req, res) => {
    const { date, type, userId = req.user.id } = req.body;
    try {
        const todayDate = new Date();

        // 先查找是否有对应日期的菜品记录
        let dateDish = await DateDish.findOne({ where: { date } });

        // 如果没有菜品记录，创建新的菜品记录
        if (!dateDish) {
            dateDish = await DateDish.create({ date });
        }

        // 查找用户是否已经为该日期预定过菜品
        const temp = await UserDateDish.findOne({ where: { userId, dateId: dateDish.dataValues.id } });

        // 如果用户没有预定过，创建新的预定记录
        if (!temp) {
            await UserDateDish.create({
                userId: userId,
                dateId: dateDish.dataValues.id,
                [type]: 1,
                orderingDate: todayDate
            });
            res.json({ status: 200, message: '预定成功' });
        }
        // 如果用户已预定过，更新现有的预定记录
        else {
            await UserDateDish.update(
                { [type]: 1, orderingDate: todayDate },
                { where: { userId, dateId: dateDish.dataValues.id } }
            );
            res.status(200).json({ status: 200, message: `您已成功预定该日期${MEALTYPEENUM[type]}了` });
        }
    }
    catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}


exports.cancelDate = async (req, res) => {
    const { date, type, userId = req.user.id } = req.body;
    // const { id: userId } = sreq.user;
    try {
        const dateDish = await DateDish.findOne({ where: { date } });
        const temp = await UserDateDish.findOne({ where: { userId, dateId: dateDish ? dateDish.dataValues.id : null } })
        if (temp) {
            // await UserDateDish.destroy({ where: { userId, dateId: dateDish.dataValues.id } });
            // await DateDish.decrement('companyNumber', { by: 1, where: { date } });
            // 更新后拿到最新的数据
            await UserDateDish.update({ [type]: 0 }, { where: { userId, dateId: dateDish ? dateDish.dataValues.id : null } });
            // 查询是否需要删除该记录
            const ifNeedDelete = await UserDateDish.findOne({ where: { userId, dateId: dateDish ? dateDish.dataValues.id : null } });
            // 如果breakfast、lunch、dinner都为0，则删除该记录
            if (ifNeedDelete.dataValues.breakfast === false && ifNeedDelete.dataValues.lunch === false && ifNeedDelete.dataValues.dinner === false) {
                await UserDateDish.destroy({ where: { userId, dateId: dateDish ? dateDish.dataValues.id : null } });
            }
            res.json({ status: 200, message: '取消成功' });
        }
    }
    catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

// exports.getDateDishUser = async (req, res) => {
//     try {
//         const { date, page, pageSize } = req.body;

//         const dateDish = await DateDish.findOne({ where: { date } });
//         if (dateDish) {
//             const temp = await UserDateDish.findAll({
//                 where: { dateId: dateDish.dataValues.id }, offset: (page - 1) * pageSize, limit: pageSize,
//                 include: [{
//                     model: User,
//                     attributes: ['username', 'jobNumber']
//                 }]
//             });
//             if (temp) {
//                 res.json({ status: 200, message: '查询成功', data: temp });
//             }
//             else {
//                 res.status(200).json({ status: 400, message: '该日期没有用户预定' });
//             }
//         }
//     }
//     catch (error) {
//         console.log(error);
//         res.status(500).json({ error: error.message });
//     }
// }

exports.getDateDishUser = async (req, res) => {
    try {
        const { date, page, pageSize } = req.body;

        // 查询定日期的DateDish记录指
        const dateDish = await DateDish.findOne({ where: { date } });
        if (dateDish) {
            // 查询预定该日期菜品的用户信息，并进行分页
            const temp = await User.findAndCountAll({
                include: [{
                    model: DateDish,
                    where: { id: dateDish.dataValues.id },
                    through: { attributes: [] }, // 不返回连接表的属性
                    attributes: [] // 不返回DateDish模型的属性，只返回关联关系
                }],
                // attributes: ['username', 'jobNumber', 'name'], // 只选择需要的用户属性
                attributes: ['username'], // 只选择需要的用户属性
                offset: (page - 1) * pageSize,
                limit: pageSize
            });

            if (temp.count > 0) {
                res.json({ status: 200, message: '查询成功', data: temp });
            } else {
                res.status(200).json({ status: 400, message: '该日期没有用户预定' });
            }
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
};

// 统计用户在一个时间段内预定了多少次
exports.countUserDish = async (req, res) => {
    try {
        const { firstDay, lastDay, page, pageSize } = req.body;
        const { id: userId } = req.user;
        const dateDish = await DateDish.findAll({
            where: {
                date: { [Op.between]: [firstDay, lastDay] }
            },
            order: [['date', 'ASC']]
        });
        const tempDateId = dateDish.map(item => item.id);
        if (dateDish) {
            // // 法1 会收到分页查询影响 微bug
            // const temp = await UserDateDish.findAndCountAll({
            //     where: { userId, dateId: dateDish.map(item => item.id) },
            //     offset: (page - 1) * pageSize, limit: pageSize,
            // });
            // const sortedTemp = temp.rows.sort((a, b) => {
            //     return tempDateId.indexOf(a.dateId) - tempDateId.indexOf(b.dateId);
            // });

            // // 更新 temp.rows 为排序后的结果
            // temp.rows = sortedTemp;
            // if (temp) {
            //     temp.rows.map(item => {
            //         item.dataValues.date = dateDish.find(dish => dish.id === item.dateId).date;
            //     })
            //     res.json({ status: 200, message: '查询成功', data: temp.rows, totals: temp.count });
            // } else {
            //     res.status(200).json({ status: 400, message: '该时间段没有预定' });
            // }
            // 法2解决 自定义sql排序 ，需当数据量大时验证效率
            const orderField = tempDateId.map(id => `'${id}'`).join(', ');
            if (!orderField) {
                res.json({ status: 200, message: '查询成功，没有可用日期', data: [], totals: 0 });
                return
            }

            // 删除 breakfast、lunch、dinner 都为空的记录
            await UserDateDish.destroy({
                where: {
                    userId,
                    dateId: tempDateId,
                    breakfast: 0,
                    lunch: 0,
                    dinner: 0
                }
            });
            // 使用 Sequelize.literal 来插入自定义的 SQL 排序语句
            const temp = await UserDateDish.findAndCountAll({
                where: { userId, dateId: tempDateId },
                offset: (page - 1) * pageSize,
                limit: pageSize,
                order: [
                    [Sequelize.literal(`FIELD(dateId, ${orderField})`), 'ASC']
                ]
            });
            if (temp) {
                temp.rows.map(item => {
                    item.dataValues.date = dateDish.find(dish => dish.id === item.dateId).date;
                })
                res.json({ status: 200, message: '查询成功', data: temp.rows, totals: temp.count });
            } else {
                res.status(200).json({ status: 400, message: '该时间段没有预定' });
            }
        } else {
            res.status(200).json({ status: 400, message: '该时间段没有预定' });
        }
    }
    catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

// 导个excel表就行，这个时间段内谁吃了几次
exports.exportUserDishOLD = async (req, res) => {
    try {
        const { firstDay, lastDay } = req.body;
        const dateDish = await DateDish.findAll({ where: { date: { [Op.between]: [firstDay, lastDay] } } });
        if (dateDish) {
            const temp = await UserDateDish.findAll({
                where: { dateId: dateDish.map(item => item.dataValues.id) },
            });

            const result = {}; // 初始化结果对象

            // 遍历temp数组，统计每个用户吃的次数
            for (const item of temp) {
                if (!result[item.userId]) {
                    result[item.userId] = { breakfast: 0, lunch: 0, dinner: 0, user: null };
                    result[item.userId].user = await User.findOne({ where: { id: item.userId }, attributes: ['username'] });
                }
                if (item.breakfast) {
                    result[item.userId].breakfast++;
                }
                if (item.lunch) {
                    result[item.userId].lunch++;
                }
                if (item.dinner) {
                    result[item.userId].dinner++;
                }
            }
            // 将result对象变成数组
            const resultArray = Object.values(result);

            if (temp) {
                res.json({ status: 200, message: '查询成功', data: resultArray });
            }
        }
    }
    catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}


exports.exportUserDish = async (req, res) => {
    try {
        const { firstDay, lastDay, pageSize = 10, page = 1 } = req.body;

        // 查询在指定日期范围内的订餐记录
        const dateDishs = await DateDish.findAll({
            where: { date: { [Op.between]: [firstDay, lastDay] } }
        });
        const dateDishIds = dateDishs.map(dateDish => dateDish.id);

        // 查询所有订餐记录，并关联用户
        const userDishes = await UserDateDish.findAll({
            where: { dateId: { [Op.in]: dateDishIds } },
            include: [{ model: User, attributes: ['id', 'username'] }]
        });

        // 统计每个用户的点餐次数
        const userDishMap = {};
        userDishes.forEach(({ userId, breakfast, lunch, dinner, User }) => {
            if (!userDishMap[userId]) {
                userDishMap[userId] = { breakfast: 0, lunch: 0, dinner: 0, user: User };
            }
            if (breakfast) userDishMap[userId].breakfast++;
            if (lunch) userDishMap[userId].lunch++;
            if (dinner) userDishMap[userId].dinner++;
        });

        // 转换为数组并按照用户名排序
        const resultArray = Object.values(userDishMap).sort((a, b) => {
            const usernameA = a.user.username.toLowerCase();
            const usernameB = b.user.username.toLowerCase();
            return usernameA.localeCompare(usernameB, 'zh-CN');
        });

        // 计算总数
        const total = resultArray.length;

        // 分页
        const paginatedResult = resultArray.slice((page - 1) * pageSize, page * pageSize);

        res.json({ status: 200, message: '查询成功', data: paginatedResult, total });
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
};


// 查询某一天的订餐明细
exports.getOneDayAllDishDates = async (req, res) => {
    try {
        const { type, date, pageSize = 10, page = 1 } = req.body;
        // 先找某天的dateId
        const dateDish = await DateDish.findOne({ where: { date } });
        if (dateDish) {
            const temp = await UserDateDish.findAndCountAll({
                where: {
                    [type]: '1',
                    dateId: dateDish.id
                },
                include: [{
                    model: User,
                    attributes: ['username']
                }],
                limit: pageSize,
                offset: (page - 1) * pageSize,
                order: [
                    // 保证中文排序正确不乱序
                    [Sequelize.literal("CONVERT(User.username USING gbk)"), "ASC"]
                ]
            });
            if (temp) {
                const result = temp.rows.map(item => ({
                    [type]: item.dataValues[type],
                    username: item.User.username,
                    orderingDate: item.dataValues.orderingDate,
                }))
                res.json({ status: 200, message: '查询成功', data: result, total: temp.count });
            }
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

// 根据日期 用户工号或者姓名查询某日信息 --> 修改,因为当出现重名时问题太复杂
// exports.searchDetailUserDateDish = async (req, res) => {
//     try {
//         const { jobNumber = '', date, name = '' } = req.body;
//         console.log(jobNumber, date, name);

//         const dateId = await DateDish.findOne({ where: { date } });
//         if (!dateId) {

//             res.json({ status: 200, message: '查询成功', data: [] });
//         } else {
//             const users = await User.findAll({
//                 where: {
//                     [Op.or]: [
//                         { jobNumber: jobNumber },
//                         { name: name }
//                     ]
//                 }
//             });
//             if (!users.length) {
//                 res.json({ status: 200, message: '并无此人', data: [] });
//             } else {
//                 const userDateDishs = await UserDateDish.findAll({
//                     where: { userId: users.map(user => user.id), dateId: dateId.id }
//                 });
//                 if (!userDateDishs.length) {
//                     const newUserDateDish = await UserDateDish.create({ userId: users.map(user => user.id), dateId: dateId.id });
//                     const result = users.map(user => {
//                         return {
//                             id: user.id,
//                             name: user.name,
//                             jobNumber: user.jobNumber,
//                         }
//                     })
//                     const createTemp = await UserDateDish.findAll({
//                         where: { userId: users.map(user => user.id), dateId: dateId.id },
//                     })
//                     //将对应的createTemp放入result中

//                     res.json({ status: 200, message: '创建', data: result });
//                     return
//                 }
//                 res.json({ status: 200, message: '查询成功', data: userDateDishs });
//             }
//         }
//     } catch (error) {
//         console.log(error);
//         res.status(500).json({ error: error.message });
//     }
// }

exports.searchDetailUserDateDish = async (req, res) => {
    try {
        const { username = '', date } = req.body;

        let dateId = await DateDish.findOne({ where: { date } });
        if (!dateId) {
            dateId = await DateDish.create({ date });
            // res.json({ status: 200, message: '该日不存在菜品安排', data: [] });
        }
        const user = await User.findOne({
            where: {
                username: username,
            }
        });
        if (!user) {
            res.json({ status: 200, message: '查无此人', data: [] });
        }
        else {
            const userDateDishs = await UserDateDish.findOrCreate({
                where: { userId: user.id, dateId: dateId.id },
                defaults: {
                    userId: user.id,
                    dateId: dateId.id,
                    breakfast: 0,
                    lunch: 0,
                    dinner: 0,
                    orderingDate: new Date()
                }
            })
            // userDateDishs[0].dataValues.name = user.name;
            // userDateDishs[0].dataValues.jobNumber = user.jobNumber;
            userDateDishs[0].dataValues.username = user.username;
            userDateDishs[0].dataValues.userId = user.id;
            res.json({ status: 200, message: '查询成功', data: [userDateDishs[0]] });
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}

// 查询某人在某段时间内所有的订餐记录
exports.getUserAllDateDishs = async (req, res) => {
    try {
        const { userId, startDate, endDate, page, pageSize } = req.body;
        // 查询用户信息
        const user = await User.findOne({ attributes: ['username'], where: { id: userId } });
        if (userId && startDate && endDate) {
            const dataDishRes = await DateDish.findAll({
                where: {
                    date: {
                        [Op.between]: [startDate, endDate]
                    }
                }
            });
            const dateIds = dataDishRes.map(item => item.id)
            const userDateDishs = await UserDateDish.findAndCountAll({
                where: {
                    userId,
                    dateId: {
                        [Op.in]: dateIds
                    }
                },
                include: [{
                    model: DateDish,
                    attributes: ['date']
                }],
                limit: pageSize,
                offset: (page - 1) * pageSize
            });
            res.json({ status: 200, message: '查询成功', data: userDateDishs.rows, username: user.username, total: userDateDishs.count });
        }
    } catch (error) {
        console.log(error);
        res.status(500).json({ error: error.message });
    }
}